package db;

import beans.ExpenseBean;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ExpenseDAO extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public ExpenseDAO() {
        connection = new MySQLConnectionBean();
    }

    /**
     * 添加用车费用报销记录
     */
    public boolean addExpense(ExpenseBean expense) throws SQLException {
        String sql = "INSERT INTO expenserecords (ExpenseID, Date, CardID, Type, Description, Amount, ApprovedBy) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, expense.getExpenseId());
            stmt.setDate(2, new java.sql.Date(expense.getDate().getTime()));
            stmt.setInt(3, expense.getCardId());
            stmt.setString(4, expense.getType());
            stmt.setString(5, expense.getDescription());
            stmt.setBigDecimal(6, expense.getAmount());
            stmt.setInt(7, expense.getApprovedBy());

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 根据ID获取报销记录
     */
    public ExpenseBean getExpenseById(int expenseId) throws SQLException {
        String sql = "SELECT e.*" +
                "FROM expenserecords e " +
                "WHERE e.ExpenseID = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, expenseId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToExpense(rs);
                }
            }
        }
        return null;
    }

    /**
     * 获取所有报销记录
     */
    public List<ExpenseBean> getExpenseRecords() throws SQLException {
        List<ExpenseBean> records = new ArrayList<>();
        String sql = "SELECT e.*" +
                "FROM expenserecords e " ;

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                records.add(mapResultSetToExpense(rs));
            }
        }
        return records;
    }

    /**
     * 根据条件搜索报销记录
     */
    public List<ExpenseBean> searchExpenseRecords(String cardNumber, String type,
                                                  String startDate, String endDate) throws SQLException {
        List<ExpenseBean> records = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT e.*, emp.employeeName AS approverName " +
                        "FROM expenserecords e " +
                        "LEFT JOIN employees emp ON e.ApprovedBy = emp.employeeID " +
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();

        // 卡号条件
        if (cardNumber != null && !cardNumber.isEmpty()) {
            sql.append(" AND c.card_number LIKE ?");
            params.add("%" + cardNumber + "%");
        }

        // 类型条件
        if (type != null && !type.isEmpty()) {
            sql.append(" AND e.Type = ?");
            params.add(type);
        }

        // 开始日期条件
        if (startDate != null && !startDate.isEmpty()) {
            sql.append(" AND DATE(e.Date) >= ?");
            params.add(Date.valueOf(startDate));
        }

        // 结束日期条件
        if (endDate != null && !endDate.isEmpty()) {
            sql.append(" AND DATE(e.Date) <= ?");
            params.add(Date.valueOf(endDate));
        }

        // 添加排序
        sql.append(" ORDER BY e.Date DESC");

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(mapResultSetToExpense(rs));
                }
            }
        }
        return records;
    }

    /**
     * 更新报销记录状态
     */
    public boolean updateExpenseStatus(int expenseId, int approvedBy) throws SQLException {
        String sql = "UPDATE expenserecords SET ApprovedBy = ? WHERE ExpenseID = ?";

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, approvedBy);
            stmt.setInt(2, expenseId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 删除报销记录
     */
    public boolean deleteExpense(int expenseId) throws SQLException {
        String sql = "DELETE FROM expenserecords WHERE ExpenseID = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, expenseId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 将ResultSet映射为Expense对象
     */
    private ExpenseBean mapResultSetToExpense(ResultSet rs) throws SQLException {
        ExpenseBean expense = new ExpenseBean();
        expense.setExpenseId(rs.getInt("ExpenseID"));
        expense.setDate(rs.getDate("Date"));
        expense.setCardId(Integer.parseInt(rs.getString("CardID")));
        expense.setType(rs.getString("Type"));
        expense.setDescription(rs.getString("Description"));
        expense.setAmount(rs.getBigDecimal("Amount"));
        expense.setApprovedBy(rs.getInt("ApprovedBy"));
        expense.setApproverName(rs.getString("approverName"));
        expense.setCardNumber(rs.getString("cardNumber"));
        return expense;
    }
}